﻿CREATE PROCEDURE [dbo].[proc_Project_Getlist_xzyf]
(
    @CompanyId int,
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),	
	@StartIndex int,
	@EndIndex INT,
	@noe_flag INT
)

AS
;WITH list As(
-- 
--SELECT ROW_NUMBER() OVER (ORDER BY Id)AS Row,
--	orderid,ProjectId,ProductName,
--CostPrice AS iad
--,cusname
--FROM  Project 
--where	companyId=@companyId AND CostPrice>0 AND FinanceAudit=1 AND Convert(nchar(10),CreateDate,120) BETWEEN @StartDate and @EndDate
	SELECT ROW_NUMBER() OVER (ORDER BY CusName)AS Row,* FROM ( SELECT orderid,sum(iad) iad,cusname FROM (
	Select id,orderid,ProjectId,
ProductName,CostPrice AS iad,CusName From Project Where CompanyId =@CompanyId 
and Convert(nchar(10),CreateDate,120) BETWEEN @StartDate and @EndDate and FinanceAudit=1
 --AND (NOE_Flag<>2)   AND 	oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
AND CostPrice!=0
  UNION
  SELECT 0,oid,oid,'',dch_yf AS iad,CusName
    FROM Order_Change_Hedge WHERE CompanyId=@companyId
   and Convert(nchar(10),UpTime,120)  BETWEEN @StartDate and @EndDate AND dch_yf!=0	
  UNION
  Select id,orderid,ProjectId,
ProductName,CostPrice AS iad,CusName From Project Where CompanyId =134 and FinanceAudit=2 
			and Convert(nchar(10),CreateDate,120) BETWEEN @StartDate and @EndDate and FinanceAuditDate>@EndDate+' 23:59:59'
) AS a GROUP BY orderid,cusname)  AS nb
   
)	
	
Select *,(SELECT COUNT(1) FROM list) AS DataCount,(SELECT SUM(iad) FROM list) AS DataSum From list Where Row between @StartIndex and @EndIndex  
	
RETURN
